﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Collections.ObjectModel;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class OfferDataAccess
    {
        private const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";
        public static Collection<Offer> GetOfferByBuyerId(long buyerId)
        {
            Collection<Offer> offerList = new Collection<Offer>();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM OFFER WHERE BuyerId=@buyerId";
                command.Parameters.AddWithValue("buyerId", buyerId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                { return null; }
                while (reader.Read())
                {
                    Offer offer = new Offer();  
                    offer.BuyerId = buyerId;
                    offer.OfferId = Int64.Parse(reader["OfferId"].ToString(), CultureInfo.CurrentCulture);
                    long id = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    Product product = new Product();
                    product = ProductDataAccess.GetProductById(id);
                    offer.RelatedProduct = product;
                    offer.OfferPrice = float.Parse(reader["OfferPrice"].ToString(), CultureInfo.CurrentCulture);
                    offer.OfferDate = DateTime.Parse(reader["OfferDate"].ToString(), CultureInfo.CurrentCulture);
                    offer.SecondOffer = float.Parse(reader["SecondOffer"].ToString(), CultureInfo.CurrentCulture);
                    offer.Status = Convert.ToString(reader["Condition"].ToString(), CultureInfo.CurrentCulture);
                    offer.Message = Convert.ToString(reader["Message"].ToString(), CultureInfo.CurrentCulture);
                    offerList.Add(offer);
                }
            }
            catch (SqlException ex)
            {

                throw new DataAccessException("No se pudo obtener los productos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return offerList;
        }
        public static Offer GetASingleOffer(long buyerId, long productId)
        {
            Offer offer = new Offer();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM OFFER WHERE ProductId=@productId and BuyerId=@buyerId";
                command.Parameters.AddWithValue("productId", productId);
                command.Parameters.AddWithValue("buyerId", buyerId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                { return null; }
                while (reader.Read())
                {
                    offer.BuyerId = buyerId;
                    offer.OfferId = Int64.Parse(reader["OfferId"].ToString(), CultureInfo.CurrentCulture);
                    offer.RelatedProduct = ProductDataAccess.GetProductById(productId);
                    offer.OfferPrice = float.Parse(reader["OfferPrice"].ToString(), CultureInfo.CurrentCulture);
                    offer.OfferDate = DateTime.Parse(reader["OfferDate"].ToString(), CultureInfo.CurrentCulture);
                    offer.SecondOffer = float.Parse(reader["SecondOffer"].ToString(), CultureInfo.CurrentCulture);
                    offer.Status = Convert.ToString(reader["Condition"].ToString(), CultureInfo.CurrentCulture);
                    offer.Message = Convert.ToString(reader["Message"].ToString(), CultureInfo.CurrentCulture);
                }
            }
            catch (SqlException ex)
            {

                throw new DataAccessException("No se pudo obtener los productos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return offer;
        }
        public static Collection<Offer> GetOffer(long productId)
        {
            Collection<Offer> offerList = new Collection<Offer>();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM OFFER WHERE ProductId=@productId";
                command.Parameters.AddWithValue("productId", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                { return null; }
                while (reader.Read())
                {
                    Offer offer = new Offer();
                    offer.OfferId = Int64.Parse(reader["OfferId"].ToString(), CultureInfo.CurrentCulture);
                    offer.BuyerId = Int64.Parse(reader["BuyerId"].ToString(), CultureInfo.CurrentCulture);
                    offer.RelatedProduct = ProductDataAccess.GetProductById(productId);
                    offer.OfferPrice = float.Parse(reader["OfferPrice"].ToString(), CultureInfo.CurrentCulture);
                    offer.OfferDate = DateTime.Parse(reader["OfferDate"].ToString(), CultureInfo.CurrentCulture);
                    offer.SecondOffer = float.Parse(reader["SecondOffer"].ToString(), CultureInfo.CurrentCulture);
                    offer.Status = Convert.ToString(reader["Condition"].ToString(), CultureInfo.CurrentCulture);
                    offer.Message=Convert.ToString(reader["Message"].ToString(),CultureInfo.CurrentCulture);
                    offerList.Add(offer);
                }
            }
            catch (SqlException ex)
            {

                throw new DataAccessException("No se pudo obtener los productos", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return offerList;
        }
        public static void InsertOffer(Offer offer)
        {
            if (offer != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO OFFER (BuyerId, ProductId, OfferDate,OfferPrice, SecondOffer, Condition,Message) VALUES (@buyerId, @productId,@offerDate,@offerPrice,@secondOffer,@status,@message)";
                    cmd.Parameters.AddWithValue("buyerId", offer.BuyerId);
                    cmd.Parameters.AddWithValue("productId", offer.RelatedProduct.ProductId);
                    cmd.Parameters.AddWithValue("offerDate", offer.OfferDate);
                    cmd.Parameters.AddWithValue("offerPrice", offer.OfferPrice);
                    cmd.Parameters.AddWithValue("secondOffer", -1);
                    cmd.Parameters.AddWithValue("status", offer.Status);
                    cmd.Parameters.AddWithValue("message", offer.Message);
                    connection.Open();
                    SqlCommand getID = connection.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting offer1");
                    }
                    offer.OfferId = Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting offer2", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
        }
        public static void ModifySecondOffer(Offer offer)
        {
            if (offer != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "UPDATE OFFER SET SecondOffer=@counterOffer, Condition=@status WHERE OfferId=@offerId";
                    command.Parameters.AddWithValue("offerId", offer.OfferId);
                    command.Parameters.AddWithValue("counterOffer", offer.SecondOffer);
                    command.Parameters.AddWithValue("status", offer.Status);
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error updating counter offer");
                    }
                }
                catch (SqlException ex)
                { throw new DataAccessException("Error updating counter offer", ex); }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
        }
    }
}
